﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;

namespace SQLServer
{
    
    public  class DBHelper
    {

        //数据库连接字符串   Integrated Security=True;
       
        private static readonly string _connStr = ConfigurationManager.ConnectionStrings["connStr"] != null ? ConfigurationManager.ConnectionStrings["connStr"].ConnectionString : "";

        

       

        /// <summary>
        /// 执行SQL语句或存储过程，返回第一行第一列值,增删改
        /// </summary>
        /// <param name="cmdType">SQL命令类型</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="sqlParam">SQL参数,可选</param>
        /// <returns>结果值</returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] sqlParam)
        {
            SqlCommand sqlCmd;
            object result;

            sqlCmd = new SqlCommand();
            result = null;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                result = sqlCmd.ExecuteScalar();
            }

            sqlCmd.Parameters.Clear();
            sqlCmd.Dispose();
            return result;
        }
        /// <summary>
        /// 查询，执行存储过程或SQL语句返回SqlDataReader实例,获取DataReader  非断开式访问
        /// </summary>
        /// <param name="cmdType">SQL命令类型</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="sqlParam">SQL参数,可选</param>
        /// <returns>SqlDataReader实例</returns>
        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] sqlParam)
        {
            SqlCommand sqlCmd;
            SqlDataReader result;
            SqlConnection conn;

            conn = new SqlConnection(_connStr);
            sqlCmd = new SqlCommand();

            try
            {
                PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                result = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
                sqlCmd.Parameters.Clear();
                sqlCmd.Dispose();
                return result;
            }
            catch
            {
                sqlCmd.Dispose();
                conn.Close();
                throw;
            }
        }
        /// <summary>
        /// 查询，执行存储过程或SQL语句返回数据集 ,获取DataSet 断开式访问
        /// </summary>
        /// <param name="cmdType">SQL命令类型</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="sqlParam">SQL参数,可选</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] sqlParam)
        {
            SqlCommand sqlCmd;
            SqlDataAdapter sqlAdapter;
            DataSet result;

            sqlCmd = new SqlCommand();
            result = new DataSet();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                sqlAdapter = new SqlDataAdapter(sqlCmd);
                sqlAdapter.Fill(result);
            }

            sqlCmd.Dispose();
            sqlAdapter.Dispose();
            return result;
        }
        /// <summary>
        /// 执行存储过程或SQL语句返回数据表,datatale
        /// </summary>
        /// <param name="cmdType">SQL命令类型</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="sqlParam">SQL参数,可选</param>
        /// <returns>数据表</returns>
        public static DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params SqlParameter[] sqlParam)
        {
            SqlCommand sqlCmd;
            DataTable result;

            sqlCmd = new SqlCommand();
            result = new DataTable();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                result.Load(sqlCmd.ExecuteReader());
            }

            sqlCmd.Dispose();
            return result;
        }
        /// <summary>
        /// 初始化SqlCommand参数
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <param name="conn"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="sqlParam"></param>
        private static void PrepareCommand(SqlCommand sqlCmd, SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] sqlParam)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
           
            sqlCmd.Connection = conn;
            sqlCmd.CommandText = cmdText;
            sqlCmd.CommandType = cmdType;

            if (sqlParam != null && sqlParam.Length > 0)
            {
                sqlCmd.Parameters.AddRange(sqlParam);
            }
        }
    }
}
